--
-- Contains the schemas for the TRL Admin Area
--


CREATE TABLE MembershipTypes (
    membershipTypeId INT IDENTITY PRIMARY KEY,
    typeName VARCHAR(100)
)

INSERT INTO MembershipTypes VALUES ('Individual')
INSERT INTO MembershipTypes VALUES ('Family')

CREATE TABLE Memberships (
    membershipId INT IDENTITY PRIMARY KEY,
    
    membershipType INT REFERENCES MembershipTypes,
    
    membershipExpiration	DATETIME,
    
    firstName	VARCHAR(100),
    lastname	VARCHAR(100),
    address1	VARCHAR(100),
    address2	VARCHAR(100),
    city		VARCHAR(100),
    state		CHAR(2),
    postalCode	VARCHAR(9),
    phoneNumber	VARCHAR(10),
    email		VARCHAR(100),

    -- If a family membership, these fields store
    -- the second member firstname, lastname, and email
    secondFirstName VARCHAR(100),
    secondLastName  VARCHAR(100),
    secondEmail		VARCHAR(100),
    
    forumUsername	VARCHAR(50),
    memberSince		DATETIME,
    
    sendPaperNewsletter BIT,	
    sendENewsletter		BIT,	
    okayToContact		BIT,
    
    notes			TEXT,
    
    needWelcomeKit BIT NOT NULL DEFAULT 0,
    welcomeKitLastSentOn DATETIME,
    welcomeKitLastSentByFullName VARCHAR(100),  -- This is displayed if welcomeKitLastSentByUserId is NULL, which it would be only in legacy
                                                -- memberships.
    welcomeKitLastSentByUserId UNIQUEIDENTIFIER -- Intentinonally not a foreign key as rows from aspnet_Users can be removed when a user is deleted.
)


--
-- Stores information about TRL Admin Users. Note that
-- the email, username, and password is stored in the aspnet_Users tables
--
CREATE TABLE AdminUserInfo (
    adminUserInfoId INT IDENTITY PRIMARY KEY,

    userId UNIQUEIDENTIFIER,

    fullName VARCHAR(100),
    lastModifiedBy UNIQUEIDENTIFIER, -- Intentinonally not a foreign key as rows from aspnet_Users can be removed when a user is deleted
    lastModifiedOn DATETIME	
)



/*
 * This two tables below are not currently used but they could be if we decide to store the history of
 * memberships.
*/
-- Member created
-- Member renewed
-- Member updated
CREATE TABLE MembershipActions (
    actionId INT IDENTITY PRIMARY KEY,
    actionName VARCHAR(100)
)

CREATE TABLE MembershipHistory (
    membershipHistoryId INT PRIMARY KEY,
    actionType INT REFERENCES MembershipActions,
    
    occuredOn DATETIME,
    occredBy INT REFERENCES Users,
    
    actionTaken INT REFERENCES Actions,
    notes TEXT
)


--
-- Tables for storing member race resutls
--
CREATE TABLE Races (
    raceId INT IDENTITY PRIMARY KEY,
    name VARCHAR(100),
    dateOfRace DATE,
    city VARCHAR(100),
    region CHAR(2)
)

CREATE TABLE RaceParticipants (
    participantId INT IDENTITY PRIMARY KEY,
    raceId INT REFERENCES Races NOT NULL,
    
    name VARCHAR(100),
    time TIME,
    racePlace INT,
    gender CHAR(1) CHECK (gender IS NULL or gender IN ('M','F')),
    
    legacyGenderPlace INT -- The gender place of the participant within TRL finishers. We now dynamically calculate this since we have gender.
                          -- We use this column to determine the order to display participants in races results that we do not have gender, 
                          -- which are only race results from the legacy database.
)




CREATE TABLE [dbo].[InterimMemberships](
	[interimMembershipId] [int] IDENTITY(1,1) NOT NULL,
	[membershipType] [int] NULL,
	[membershipExpiration] [datetime] NULL,
	[firstName] [varchar](100) NULL,
	[lastName] [varchar](100) NULL,
	[address] [varchar](100) NULL,
	[city] [varchar](100) NULL,
	[state] [char](2) NULL,
	[postalCode] [varchar](9) NULL,
	[phoneNumber] [varchar](10) NULL,
	[email] [varchar](100) NULL,
	[forumUsername] [varchar](50) NULL,
	[memberSince] [datetime] NULL,
	[sendPaperNewsletter] [bit] NULL,
	[sendENewsletter] [bit] NULL,
	[okayToContact] [bit] NULL,
	[notes] [text] NULL,
	[secondFirstName] [varchar](100) NULL,
	[secondLastName] [varchar](100) NULL,
	[secondEmail] [varchar](100) NULL,
	[secondForumUsername] [varchar](50) NULL,
	[welcomeKitLastSentOn] [datetime] NULL,
	[welcomeKitLastSentByFullName] [varchar](100) NULL,
	[welcomeKitLastSentByUserId] [uniqueidentifier] NULL,
	[needWelcomeKit] [bit] NOT NULL,
	[dob] [datetime] NULL,
	[secondDob] [datetime] NULL,
	[occupation] [varchar](100) NULL,
	[secondOccupation] [varchar](100) NULL,
	[gender] [varchar](1) NULL,
	[secondGender] [varchar](1) NULL,
	[shirtSize] [varchar](2) NULL,
	[secondShirtSize] [varchar](2) NULL,
	
	[IPAddressFrom] VARCHAR(10),
	[addedOn] DATETIME,
	[userAgent] VARCHAR(100),
	[newOrRenewal] CHAR(1),
	[paypalToken] VARCHAR(100)
PRIMARY KEY CLUSTERED 
(
	[interimMembershipId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]


